﻿'Description:批次检验类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Yuxj
'create Date:2017-10-31
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Data.OracleClient
Public Class uLMSExchangeBillBusiness
    Implements IBusiness.uLMSExchangeBillIbusiness

    Function GetExchangeBillSign(ByVal ExchangeBillID As String) As DataTable Implements uLMSExchangeBillIbusiness.GetExchangeBillSign
        Dim strsql As String = <string>
                                    SELECT EX.BILLSTATUS, f.factoryid,f.factoryname,u.userattributename,u.attributevalue factoryCode,ex.operateemployeename fullname 
                                    FROM EXCHANGEBILLDEALINFO ex
                                    LEFT JOIN factory f ON ex.factoryid=f.factoryid 
                                    LEFT JOIN Userattribute u ON f.factoryid=u.parentid AND u.userattributename='分厂代码'                                                                         
                               </string>
        strsql += String.Format(" where  ex.exchangebillid= '{0}'", ExchangeBillID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function
   
    ''' <summary>
    ''' 删除周转单/串件单
    ''' create Yuxj 20171031
    ''' </summary>
    ''' <param name="ExchangeBillID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function DelExchangeBill(ByVal ExchangeBillID As String) As Boolean Implements uLMSExchangeBillIbusiness.DelExchangeBill
        Dim strsql As String = String.Format("UPDATE exchangebill  SET ISUSE='1',ISTRANSFER='0' WHERE  exchangeid= '{0}'", ExchangeBillID)
        If (DatabaseHelper.ExecuteSql(strsql) > 0) Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 插入周转单/串件单资料
    ''' 增加实际值的记录Add by Yuxj20190403
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function InsertExchangeBill(ByVal entity As ExchangeBillInfoDTO) As Boolean Implements uLMSExchangeBillIbusiness.InsertExchangeBill
        Dim strsql As String =
            <string>
                INSERT INTO exchangebill
                  (billproperty, billsource,billstatus, productid, billtype, containerid, createdate, employeeid, exchangeid, exchangename, machine, maprequest, operatedate, reason, 
                    workhours, istransfer, isuse,comContainerName,Note,ContainerNo,ACTUAL,factoryID,
                    exchangemethod, exchangecodeno, erpbatchno, factoryworkhours, qty, upexchangecodeno, upcontainerno, 
                    upqty, upbatchno, totalworkhours, isoverfactorytime, exchangetype)
                VALUES
                   (:billproperty, :billsource,'0', :productid, :billtype, :containerid, to_date(:createdate,'YYYY-MM-DD'), 
                    :employeeid, :exchangeid, :exchangename, :machine, :maprequest, sysdate, :reason, :workhours, 
                    :istransfer, :isuse,:comContainerName,:Note,:ContainerNo,:ACTUAL,:FactoryID,
                    :exchangemethod, :exchangecodeno, :erpbatchno, :factoryworkhours, :qty, :upexchangecodeno, :upcontainerno, 
                    :upqty, :upbatchno, :totalworkhours, :isoverfactorytime, :exchangetype)
            </string>
        Dim insertpar(31) As OracleParameter
        insertpar(0) = DatabaseHelper.NewParameter(":billproperty", entity.BillProperty, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":billsource", entity.BillSource, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":productid", entity.ProductID, OracleType.VarChar)
        insertpar(3) = DatabaseHelper.NewParameter(":billtype", entity.BillType, OracleType.VarChar)
        insertpar(4) = DatabaseHelper.NewParameter(":containerid", entity.ContainerID, OracleType.VarChar)
        insertpar(5) = DatabaseHelper.NewParameter(":createdate", entity.CreateDate, OracleType.VarChar)
        insertpar(6) = DatabaseHelper.NewParameter(":employeeid", entity.EmployeeID, OracleType.VarChar)
        insertpar(7) = DatabaseHelper.NewParameter(":exchangeid", entity.ExchangeID, OracleType.VarChar)
        insertpar(8) = DatabaseHelper.NewParameter(":exchangename", entity.ExchangeName, OracleType.VarChar)
        insertpar(9) = DatabaseHelper.NewParameter(":machine", entity.Machine, OracleType.VarChar)
        insertpar(10) = DatabaseHelper.NewParameter(":maprequest", entity.MapRequest, OracleType.VarChar)
        insertpar(11) = DatabaseHelper.NewParameter(":reason", entity.Reason, OracleType.VarChar)
        insertpar(12) = DatabaseHelper.NewParameter(":workhours", entity.WorkHours, OracleType.VarChar)
        insertpar(13) = DatabaseHelper.NewParameter(":istransfer", entity.IsTransfer, OracleType.VarChar)
        insertpar(14) = DatabaseHelper.NewParameter(":isuse", entity.IsUse, OracleType.VarChar)
        insertpar(15) = DatabaseHelper.NewParameter(":comContainerName", entity.ComContainerName, OracleType.VarChar)

        'Add by Yuxj20180328
        insertpar(16) = DatabaseHelper.NewParameter(":Note", entity.Note, OracleType.VarChar)
        insertpar(17) = DatabaseHelper.NewParameter(":ContainerNo", entity.ContainerNo, OracleType.VarChar)
        'Add by Yuxj20190403
        insertpar(18) = DatabaseHelper.NewParameter(":ACTUAL", entity.Actual, OracleType.VarChar)

        insertpar(19) = DatabaseHelper.NewParameter(":FactoryID", entity.FactoryID, OracleType.VarChar)

        insertpar(20) = DatabaseHelper.NewParameter(":exchangemethod", entity.ExchangeMethod, OracleType.VarChar)
        insertpar(21) = DatabaseHelper.NewParameter(":exchangecodeno", entity.ExchangeCodeNo, OracleType.VarChar)
        insertpar(22) = DatabaseHelper.NewParameter(":erpbatchno", entity.ERPBatchNo, OracleType.VarChar)
        insertpar(23) = DatabaseHelper.NewParameter(":factoryworkhours", entity.FactoryWorkHours, OracleType.VarChar)
        insertpar(24) = DatabaseHelper.NewParameter(":qty", entity.Qty, OracleType.VarChar)
        insertpar(25) = DatabaseHelper.NewParameter(":upexchangecodeno", entity.UpExchangeCodeNo, OracleType.VarChar)
        insertpar(26) = DatabaseHelper.NewParameter(":upcontainerno", entity.UpContainerNo, OracleType.VarChar)
        insertpar(27) = DatabaseHelper.NewParameter(":upqty", entity.UpQty, OracleType.VarChar)
        insertpar(28) = DatabaseHelper.NewParameter(":upbatchno", entity.UpBatchNo, OracleType.VarChar)
        insertpar(29) = DatabaseHelper.NewParameter(":totalworkhours", entity.TotalWorkHours, OracleType.VarChar)
        insertpar(30) = DatabaseHelper.NewParameter(":isoverfactorytime", entity.IsOverFactoryTime, OracleType.VarChar)
        insertpar(31) = DatabaseHelper.NewParameter(":exchangetype", entity.ExchangeType, OracleType.VarChar)

        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql, insertpar)
        If (rows = 0) Then
            Return False
        Else
            Return True
        End If
    End Function

    Private Function UpdateExchangeBill(ByVal entity As ExchangeBillInfoDTO) As Boolean Implements uLMSExchangeBillIbusiness.UpdateExchangeBill
        Dim strsql As String =
            <string>
                UPDATE EXCHANGEBILL
                   SET BILLPROPERTY = :BILLPROPERTY,
                       BILLSOURCE   = :BILLSOURCE,
                       PRODUCTID    = :PRODUCTID,                       
                       CREATEDATE   = TO_DATE(:CREATEDATE, 'YYYY-MM-DD'),
                       EMPLOYEEID   = :EMPLOYEEID,
                       MACHINE      = :MACHINE,
                       MAPREQUEST   = :MAPREQUEST,
                       REASON       = :REASON,
                       WORKHOURS    = :WORKHOURS,
                       comContainerName=:comContainerName,
                       Note=:Note,
                       exchangemethod=:exchangemethod, exchangecodeno=:exchangecodeno, erpbatchno=:erpbatchno, 
                        factoryworkhours=:factoryworkhours, qty=:qty, upexchangecodeno=:upexchangecodeno, upcontainerno=:upcontainerno, 
                       upqty=:upqty, upbatchno=:upbatchno, totalworkhours=:totalworkhours, isoverfactorytime=:isoverfactorytime, exchangetype =:exchangetype,ContainerNo=:ContainerNo
                 WHERE EXCHANGEID = :EXCHANGEID
                   AND EXCHANGENAME = :EXCHANGENAME
            </string>
        Dim insertpar(25) As OracleParameter
        insertpar(0) = DatabaseHelper.NewParameter(":billproperty", entity.BillProperty, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":billsource", entity.BillSource, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":productid", entity.ProductID, OracleType.VarChar)
        insertpar(3) = DatabaseHelper.NewParameter(":createdate", entity.CreateDate, OracleType.VarChar)
        insertpar(4) = DatabaseHelper.NewParameter(":employeeid", entity.EmployeeID, OracleType.VarChar)
        insertpar(5) = DatabaseHelper.NewParameter(":machine", entity.Machine, OracleType.VarChar)
        insertpar(6) = DatabaseHelper.NewParameter(":maprequest", entity.MapRequest, OracleType.VarChar)
        insertpar(7) = DatabaseHelper.NewParameter(":reason", entity.Reason, OracleType.VarChar)
        insertpar(8) = DatabaseHelper.NewParameter(":workhours", entity.WorkHours, OracleType.VarChar)
        insertpar(9) = DatabaseHelper.NewParameter(":comContainerName", entity.ComContainerName, OracleType.VarChar)
        insertpar(10) = DatabaseHelper.NewParameter(":exchangeid", entity.ExchangeID, OracleType.VarChar)
        insertpar(11) = DatabaseHelper.NewParameter(":exchangename", entity.ExchangeName, OracleType.VarChar)

        insertpar(12) = DatabaseHelper.NewParameter(":Note", entity.Note, OracleType.VarChar)

        insertpar(13) = DatabaseHelper.NewParameter(":exchangemethod", entity.ExchangeMethod, OracleType.VarChar)
        insertpar(14) = DatabaseHelper.NewParameter(":exchangecodeno", entity.ExchangeCodeNo, OracleType.VarChar)
        insertpar(15) = DatabaseHelper.NewParameter(":erpbatchno", entity.ERPBatchNo, OracleType.VarChar)
        insertpar(16) = DatabaseHelper.NewParameter(":factoryworkhours", entity.FactoryWorkHours, OracleType.VarChar)
        insertpar(17) = DatabaseHelper.NewParameter(":qty", entity.Qty, OracleType.VarChar)
        insertpar(18) = DatabaseHelper.NewParameter(":upexchangecodeno", entity.UpExchangeCodeNo, OracleType.VarChar)
        insertpar(19) = DatabaseHelper.NewParameter(":upcontainerno", entity.UpContainerNo, OracleType.VarChar)
        insertpar(20) = DatabaseHelper.NewParameter(":upqty", entity.UpQty, OracleType.VarChar)
        insertpar(21) = DatabaseHelper.NewParameter(":upbatchno", entity.UpBatchNo, OracleType.VarChar)
        insertpar(22) = DatabaseHelper.NewParameter(":totalworkhours", entity.TotalWorkHours, OracleType.VarChar)
        insertpar(23) = DatabaseHelper.NewParameter(":isoverfactorytime", entity.IsOverFactoryTime, OracleType.VarChar)
        insertpar(24) = DatabaseHelper.NewParameter(":exchangetype", entity.ExchangeType, OracleType.VarChar)
        insertpar(25) = DatabaseHelper.NewParameter(":ContainerNo", entity.ContainerNo, OracleType.VarChar)

        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql, insertpar)
        If (rows = 0) Then
            Return False
        End If
        Return True
    End Function

    Private Function GetUserID(ByVal strEmp As String) As String Implements uLMSExchangeBillIbusiness.GetUserID
        Dim sb As New StringBuilder
        sb.AppendFormat("select employeeid from employee e where lower(e.employeename)='{0}'", strEmp.ToLower)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            Return dt.Rows(0)("employeeid").ToString
        Else
            Return ""
        End If
    End Function

    Private Function GetProductID(ByVal strPName As String) As String Implements uLMSExchangeBillIbusiness.GetProductID
        Dim sb As New StringBuilder
        sb.AppendFormat("SELECT p.productid FROM product p ")
        sb.AppendLine(" LEFT JOIN productbase pb ON p.productid=pb.revofrcdid ")
        sb.AppendFormat(" WHERE pb.productname='{0}'", strPName)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            Return dt.Rows(0)("productid").ToString
        Else
            Return ""
        End If
    End Function

    Private Function GetContainerID(ByVal strContainer As String) As String Implements uLMSExchangeBillIbusiness.GetContainerID
        Dim sb As New StringBuilder
        sb.AppendFormat("select containerId from container c where c.containername='{0}'", strContainer)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            Return dt.Rows(0)("containerId").ToString
        Else
            Return ""
        End If
    End Function

    Private Function CheckExists(ByVal strBillId As String, ByVal strBillName As String) As Boolean Implements uLMSExchangeBillIbusiness.CheckExists
        CheckExists = False
        Dim sb As New StringBuilder
        sb.AppendFormat("select 1 from EXCHANGEBILL ex where ex.exchangeid='{0}' or ex.exchangename='{1}'", strBillId, strBillName)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            Return True
        End If
    End Function

    ''' <summary>
    ''' 串件及周转件编码
    ''' </summary>
    ''' <param name="strBillNameFix"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetNextFlowSN(ByVal strBillNameFix As String) As String Implements uLMSExchangeBillIbusiness.GetNextFlowSN
        '发动总体事业部-2017-0001
        Dim sb As New StringBuilder
        sb.AppendFormat("select max(ex.exchangename) exchangename from EXCHANGEBILL ex where ex.exchangename like '{0}%'", strBillNameFix)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            Dim currentName As String = dt.Rows(0)("exchangename").ToString
            If currentName = String.Empty Then
                GetNextFlowSN = "0001"
            Else
                GetNextFlowSN = (Convert.ToInt32(currentName.Substring(currentName.Length - 4, 4)) + 1).ToString().PadLeft(4, "0")
            End If
        Else
            GetNextFlowSN = "0001"
        End If
    End Function

    ''' <summary>
    ''' 串件及周转件页面查询
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function SearchExchangeBill(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSExchangeBillIbusiness.SearchExchangeBill
        Dim sb As StringBuilder = New StringBuilder
        sb.AppendLine(" SELECT ex.EXCHANGEID,ex.exchangename,ex.billtype,pb.productname,p.productid,ex.note,c.containername,c.containerid,ex.containerno,p.description,to_char(ex.createdate,'yyyy-mm-dd') createdate,ex.machine, ")
        sb.AppendLine(" ex.exchangemethod, ex.exchangecodeno, ex.erpbatchno, ex.factoryworkhours, ex.qty, ex.upexchangecodeno, ex.upcontainerno, ex.upqty, ex.upbatchno, ex.totalworkhours, ex.isoverfactorytime, ex.exchangetype,")
        sb.AppendLine(<string>
                          (SELECT f.FACTORYNAME FROM FACTORY f WHERE f.FACTORYID=ex.FACTORYID) FACTORYNAME
                    ,(SELECT e2.FULLNAME FROM EMPLOYEE e2 WHERE e2.EMPLOYEEID=ex.DESIGN_EMPLOYEEID) DESIGNNAME
                    ,(SELECT e3.FULLNAME FROM EMPLOYEE e3 WHERE e3.EMPLOYEEID=ex.GCJSB_EMPLOYEEID) GCJSBNAME1,to_char(ex.GCJSB_AUDITDATE ,'yyyy-MM-dd hh24:mi:ss') GCJSBAUDITDATE1
                    ,(SELECT e4.FULLNAME FROM EMPLOYEE e4 WHERE e4.EMPLOYEEID=ex.SCZHB_EMPLOYEEID) SCZHBNAME1,to_char(ex.SCZHB_AUDITDATE ,'yyyy-MM-dd hh24:mi:ss') SCZHBAUDITDATE1
                    ,(SELECT e5.FULLNAME FROM EMPLOYEE e5 WHERE e5.EMPLOYEEID=ex.JYJCSYB_EMPLOYEEID) JYJCSYBNAME1,to_char(ex.JYJCSYB_AUDITDATE ,'yyyy-MM-dd hh24:mi:ss') JYJCSYBAUDITDATE1
                    ,ex.CUSTOMERNAME,to_char(ex.CUSTOMERDEALDATE,'yyyy-MM-dd hh24:mi:ss') CUSTOMERDEALDATE
                    ,(SELECT e7.FULLNAME FROM EMPLOYEE e7 WHERE e7.EMPLOYEEID=e.OPERATEEMPLOYEEID) GCJSBNAME,to_char(e.OPERATEDATE,'yyyy-MM-dd hh24:mi:ss') GCJSBAUDITDATE
                    ,(SELECT e8.FULLNAME FROM EMPLOYEE e8 WHERE e8.EMPLOYEEID=e1.OPERATEEMPLOYEEID) JYJCSYBNAME,to_char(e1.OPERATEDATE ,'yyyy-MM-dd hh24:mi:ss')  JYJCSYBAUDITDATE
                    ,(SELECT e9.FULLNAME FROM EMPLOYEE e9 WHERE e9.EMPLOYEEID=e2.OPERATEEMPLOYEEID) SCZHBNAME,to_char(e2.OPERATEDATE ,'yyyy-MM-dd hh24:mi:ss') SCZHBAUDITDATE
                    ,(SELECT e10.FULLNAME FROM EMPLOYEE e10 WHERE e10.EMPLOYEEID=e3.OPERATEEMPLOYEEID) FINNAME,to_char(e3.OPERATEDATE ,'yyyy-MM-dd hh24:mi:ss') FINDATE,
                      </string>)
        sb.AppendLine(" ex.billsource,ex.billproperty,ex.workhours,decode(ex.billstatus,'0','已创建','1','已周转','2','已完成',ex.billstatus) billstatus ,ex.reason,ex.maprequest,ex.comContainerName FROM  EXCHANGEBILL ex  ")
        sb.AppendLine(" LEFT JOIN container c ON ex.containerid=c.containerid ")
        sb.AppendLine(" LEFT JOIN product p ON ex.productid=p.productid ")
        sb.AppendLine(" LEFT JOIN productbase pb ON p.productid=pb.revofrcdid ")
        sb.AppendLine(<string>
                         LEFT JOIN EXCHANGEBILLDEALINFO e ON e.EXCHANGEBILLID =ex.EXCHANGEID AND e.ISUSE =0 AND  e.BILLSTATUS =7--工程技术部
                         LEFT JOIN EXCHANGEBILLDEALINFO e1 ON e1.EXCHANGEBILLID =ex.EXCHANGEID AND e1.ISUSE =0  AND e1.BILLSTATUS =8--检验检测事业部
                         LEFT JOIN EXCHANGEBILLDEALINFO e2 ON e2.EXCHANGEBILLID =ex.EXCHANGEID AND e2.ISUSE =0  AND e2.BILLSTATUS =9--生产指挥部
                         LEFT JOIN EXCHANGEBILLDEALINFO e3 ON e3.EXCHANGEBILLID =ex.EXCHANGEID AND e3.ISUSE =0 AND  e3.BILLSTATUS =2--厂内确认
                      </string>)
        sb.AppendLine(" WHERE ex.isuse ='0' ")
        If para.ContainsKey("ContainerName") Then
            sb.AppendFormat(" and c.containername='{0}'", para("ContainerName"))
        End If

        If para.ContainsKey("ProductName") Then
            sb.AppendFormat(" and pb.ProductName='{0}'", para("ProductName"))
        End If

        If para.ContainsKey("Machine") Then
            sb.AppendFormat(" and ex.Machine='{0}'", para("Machine"))
        End If

        If para.ContainsKey("Step") Then
            sb.AppendFormat(" and ex.BillStep='{0}'", para("Step"))
        End If

        If para.ContainsKey("ExchangeID") Then
            sb.AppendFormat(" and ex.EXCHANGEID='{0}'", para("ExchangeID"))
        End If

        If para.ContainsKey("Status") Then
            sb.AppendFormat(" and ex.BillStatus='{0}'", para("Status"))
        End If

        If para.ContainsKey("CId") Then
            sb.AppendFormat(" and ex.containerid='{0}'", para("CId"))
        End If

        If para.ContainsKey("PartType") Then
            If para("PartType") = "1" Then
                sb.AppendFormat(" and ex.billtype='周转单'")
            Else
                sb.AppendFormat(" and ex.billtype='串件单'")
            End If
        End If

        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' 判断批次号是否存在
    ''' </summary>
    ''' <param name="strCName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function BoolCnameExists(ByVal strCName As String) As Boolean Implements uLMSExchangeBillIbusiness.BoolCnameExists
        Dim sb As StringBuilder = New StringBuilder
        sb.AppendFormat("select * from container c where c.containername='{0}'", strCName)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' Add by Yuxj20180328 获取需要创建串件单和周转单的任务单资料
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function SearchContainerNoInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSExchangeBillIbusiness.SearchContainerNoInfo
        SearchContainerNoInfo = New uMESProcessResult
        Dim sb As StringBuilder = New StringBuilder
        sb.AppendLine("SELECT c.togetherflag,c.containername,c.containerid,p.description,pb.productname,p.productid,u.attributevalue MachineName, ")
        sb.AppendLine("wfs.workflowstepname,wfs.workflowstepid,m.mfgordername,EP.TYPE ")
        If para.ContainsKey("ExchangeID") Then
            sb.AppendLine(",ex.exchangename,ex.createdate,ex.comcontainername,ex.billproperty,ex.billsource,ex.workhours,ex.reason,ex.maprequest") 'add YangJiang 20180529
        End If
        sb.AppendLine("FROM container c ")
        If para.ContainsKey("ExchangeID") Then
            sb.AppendLine(" LEFT JOIN exchangebill ex ON ex.containerid=c.containerid") 'add YangJiang 20180529
        End If
        'Modify by yuxj20200409增加施工单显示ON c.containerid=ep.containerid
        sb.AppendLine("INNER JOIN enginepartplan ep ON NVL(ep.containerid,ep.enginecontainerid)=c.containerid ")
        sb.AppendLine("LEFT JOIN product p ON p.productid=c.productid ")
        sb.AppendLine("LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid ")
        sb.AppendLine("LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid ")
        sb.AppendLine("LEFT JOIN mfgorder m ON c.mfgorderid=m.mfgorderid ")
        sb.AppendLine("LEFT JOIN Userattribute u ON m.mfgorderid=u.parentid ")
        'Modify by Yuxj20180702 修改厂别查询方式
        sb.AppendLine(" LEFT JOIN workflow wf ON wf.workflowid=ep.workflowid ")
        sb.AppendLine("WHERE u.userattributename='发动机' ")

        'sb.AppendLine(" AND EP.USECOMPLETED='0' ") 'Modify by Yuxj20200409 排除已完成的任务单
        If para.ContainsKey("UseCompleted") Then
            sb.AppendFormat(" AND EP.USECOMPLETED='{0}' ", para("UseCompleted")) 'Yuxj20200506
        End If
        ' add YangJiang 20180529
        If para.ContainsKey("ExchangeID") Then
            sb.AppendFormat(" and ex.exchangeid='{0}'", para("ExchangeID"))
        End If

        If para.ContainsKey("ContainerName") Then
            sb.AppendFormat(" and c.togetherflag like '%{0}%'", para("ContainerName"))
        End If

        'modify YangJiang 20180607 =->like
        If para.ContainsKey("ProductName") Then
            sb.AppendFormat(" and pb.ProductName like '%{0}%'", para("ProductName"))
        End If

        'modify Yuxj 20180607 Machine->MachineStart =->like
        If para.ContainsKey("MachineStart") Then
            sb.AppendFormat(" and u.attributevalue like'%{0}%'", para("MachineStart"))
        End If

        If para.ContainsKey("MachineStatus") Then
            sb.AppendFormat(" and wfs.workflowstepid='{0}'", para("MachineStatus"))
        End If

        If para.ContainsKey("FactoryId") Then
            sb.AppendFormat(" and wf.FactoryId='{0}'", para("FactoryId"))
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        SearchContainerNoInfo = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' Add by Yuxj20180328 根据批次ID获取零件目录件号列表
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetPartMenuList(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSExchangeBillIbusiness.GetPartMenuList
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT pm.productid,pb.productname FROM partmenuinfo pm ")
        sb.AppendLine("inner JOIN product p ON pm.productid=p.productid ")
        sb.AppendLine("LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid ")
        sb.AppendFormat(" where pm.containerno='{0}'", para("ContainerNO"))
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' 获取件号名称
    ''' </summary>
    ''' <param name="Pid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetProductDesc(ByVal Pid As String) As String Implements uLMSExchangeBillIbusiness.GetProductDesc
        Dim sb As New StringBuilder
        sb.AppendFormat("select description from product p where p.productid='{0}'", Pid)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt.Rows(0)("description").ToString
    End Function

    ''' <summary>
    ''' 获取周转或串件数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetExchangeBillData(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSExchangeBillIbusiness.GetExchangeBillData

        Dim strSql As String = <string>
SELECT pb.productname,e.fullname createEmp,el.billproperty,el.billsource,el.billstatus,
el.billtype,el.containerid,el.createdate,el.employeeid, 
el.exchangeid,el.exchangename,el.machine, el.maprequest, el.operatedate,
 el.productid,el.reason, el.workhours, el.istransfer, el.isuse, el.comcontainername,el.containerno, 
 el.note, el.stepinfoid, el.actual, el.factoryid   FROM EXCHANGEBILL el 
LEFT JOIN product p ON p.productid=el.productid
LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid
LEFT JOIN employee e ON e.employeeid=el.employeeid
WHERE el.isuse=0</string>.Value

        If para.ContainsKey("BillType") Then
            strSql += String.Format(" and el.Billtype='{0}' ", para("BillType"))
        End If

        If para.ContainsKey("BillStatus") Then
            strSql += String.Format(" and el.BillStatus={0} ", para("BillStatus"))
            If para("BillStatus") = "0" Then '厂内未确认

            ElseIf para("BillStatus") = "1" Then '厂内确认中

            ElseIf para("BillStatus") = "2" Then '厂外未确认

            ElseIf para("BillStatus") = "3" Then '厂外确认中

            ElseIf para("BillStatus") = "4" Then '厂外确认完

            ElseIf para("BillStatus") = "5" Then '已退回

            End If
        End If

        If para.ContainsKey("ProductName") Then
            strSql += String.Format(" and pb.ProductName='{0}' ", para("ProductName"))
        End If

        If para.ContainsKey("ContainerNo") Then
            strSql += String.Format(" and el.ContainerNo='{0}' ", para("ContainerNo"))
        End If

        If para.ContainsKey("Machine") Then
            strSql += String.Format(" and el.Machine='{0}' ", para("Machine"))
        End If

        strSql += <string> GROUP BY pb.productname,e.fullname,el.billproperty,el.billsource,el.billstatus,el.billtype,el.containerid,el.createdate,el.employeeid, el.exchangeid,el.exchangename,el.machine, el.maprequest, el.operatedate,
 el.productid,el.reason, el.workhours, el.istransfer, el.isuse, el.comcontainername,el.containerno, el.note, el.stepinfoid, el.actual, el.factoryid </string>.Value
        strSql += " order by el.createdate asc"
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql

        Return DatabaseHelper.RunProcedureForPage(paraPage)

    End Function
End Class
